﻿'Description:工序关联子工艺工序类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:tianFW
'create Date:2018年12月5日
'Rewriter:
'Rewrite Date:
Imports BusinessRule.IBusiness
Imports DatabaseAccess.DBUtility
Imports System.Text

Public Class uLMSStepRelevantToSonWFStepBusiness
    Implements uLMSStepRelevantToSonWFStepIBusiness

    ''' <summary>
    ''' 获取工序关联子工艺工序信息
    ''' </summary>
    ''' <param name="para"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function GetStepRelevantToSonWFStepInfo(ByVal para As Dictionary(Of String, String)) As DataTable Implements uLMSStepRelevantToSonWFStepIBusiness.GetStepRelevantToSonWFStepInfo
        Dim strsql As New StringBuilder
        Dim strvalue As String =
            <string>
             SELECT sbs.workflowid,
			 sbs.workflowstepid,
			 sbs.sonworkflowid,
			 ws.workflowstepname || '(' || subwb.workflowname || ':' ||
			 subwf.workflowrevision || ')'  stepandsubwfinfo,
			 sbs.sonworkflowstepid,
			 sws.workflowstepname sonworkflowstepname
             FROM stepsubstep sbs
             LEFT JOIN workflowstep ws
             ON ws.workflowstepid = sbs.workflowstepid
             LEFT JOIN workflowstep sws
             ON sws.workflowstepid = sbs.sonworkflowstepid
             LEFT JOIN workflow subwf
             ON subwf.workflowid = sbs.sonworkflowid
             LEFT JOIN workflowbase subwb
             ON subwb.workflowbaseid = subwf.workflowbaseid
            </string>
        strsql.AppendLine(strvalue)
        strsql.AppendFormat(" WHERE sbs.workflowid='{0}' AND sbs.sonworkflowid='{1}' ", para("workflowID"), para("sonWorkflowID"))
        strsql.AppendLine("ORDER BY ws.sequence,sws.sequence")
        Return DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
    End Function

    ''' <summary>
    ''' 删除原来的工序关联子工艺工序信息 创建新的关联信息
    ''' </summary>
    ''' <param name="ds"></param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function AddNewStepRelevantToSonWFSTeps(ByVal ds As DataSet) As Boolean Implements uLMSStepRelevantToSonWFStepIBusiness.AddNewStepRelevantToSonWFSTeps
        AddNewStepRelevantToSonWFSTeps = False
        Dim valuearr As New ArrayList
        If ds.Tables(0).Rows.Count = 0 Then
            Dim newdt As DataTable = ds.Tables(1).DefaultView.ToTable(True, "workflowStepID")
            For Each dr As DataRow In newdt.Rows
                valuearr.Add(String.Format("UPDATE WORKFLOWSTEP WS SET WS.WIPMSGLABEL='子工艺' WHERE ws.workflowstepid='{0}' ", dr("workflowStepID")))
            Next
        Else
            For Each dr As DataRow In ds.Tables(0).Rows
                valuearr.Add(String.Format("DELETE FROM stepsubstep sbs WHERE sbs.workflowid = '{0}' AND sbs.workflowstepid='{1}'", dr("workflowID"), dr("workflowStepID")))
            Next
        End If
        For Each dr As DataRow In ds.Tables(1).Rows
            valuearr.Add(String.Format("INSERT INTO stepsubstep(workflowid,workflowstepid,sonworkflowid,sonworkflowstepid) VALUES('{0}','{1}','{2}','{3}')", dr("workflowID"), dr("workflowStepID"), dr("sonWorkflowID"), dr("sonWorkflowStepID")))
        Next
        Try
            DatabaseHelper.ExecuteSqlTran(valuearr)
        Catch ex As Exception
            Exit Function
        End Try
        AddNewStepRelevantToSonWFSTeps = True
    End Function

    ''' <summary>
    ''' 重组工艺子工艺工序关联子工艺工序 
    ''' </summary>
    ''' <param name="newWorkflowID">重组工艺ID</param>
    ''' <param name="oldWrokflwoID">原工艺ID</param>
    ''' <param name="oldWFStepIDs">重组工艺选择的原工艺子工艺序IDs</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Public Function CZWFStepRelevantToSonWFStep(ByVal newWorkflowID As String, ByVal oldWrokflwoID As String, ByVal oldWFStepIDs As String) As Boolean Implements uLMSStepRelevantToSonWFStepIBusiness.CZWFStepRelevantToSonWFStep
        CZWFStepRelevantToSonWFStep = False
        Dim strsql As New StringBuilder
        strsql.AppendLine("SELECT ws.workflowstepid,ws.workflowstepname,sbs.sonworkflowid,sbs.sonworkflowstepid ")
        strsql.AppendLine("FROM workflowstep ws ")
        strsql.AppendLine("LEFT JOIN  stepsubstep sbs ON sbs.workflowid = ws.workflowid AND ws.workflowstepid = sbs.workflowstepid")
        strsql.AppendFormat(" WHERE ws.workflowid = '{0}' AND ws.workflowstepid IN ('{1}')", oldWrokflwoID, oldWFStepIDs)
        Dim value_dt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
        If value_dt.Rows.Count = 0 Then
            CZWFStepRelevantToSonWFStep = True
            Exit Function
        Else
            strsql.Clear()
        End If
        strsql.AppendLine("SELECT ws.workflowstepid,ws.workflowstepname　FROM workflowstep ws ")
        strsql.AppendFormat("WHERE ws.workflowid='{0}'", newWorkflowID)
        Dim newWF_WSdt As DataTable = DatabaseHelper.Query_ReturnDatTable(strsql.ToString())
        Dim ds As New DataSet
        Dim dts As DataTable() = {New DataTable, New DataTable}
        dts(1).Columns.Add("workflowID")
        dts(1).Columns.Add("workflowStepID")
        dts(1).Columns.Add("sonWorkflowID")
        dts(1).Columns.Add("sonWorkflowStepID")
        Dim newdr As DataRow
        For Each dr As DataRow In newWF_WSdt.Rows
            For Each r As DataRow In value_dt.Rows
                If dr("workflowstepname") = r("workflowstepname") Then
                    newdr = dts(1).NewRow()
                    newdr("workflowID") = newWorkflowID
                    newdr("workflowStepID") = dr("workflowstepid")
                    newdr("sonWorkflowID") = r("sonworkflowid")
                    newdr("sonWorkflowStepID") = r("sonworkflowstepid")
                    dts(1).Rows.Add(newdr)
                End If
            Next
        Next
        dts(1).AcceptChanges()
        If dts(1).Rows.Count > 0 Then
            ds.Tables.AddRange(dts)
            AddNewStepRelevantToSonWFSTeps(ds)
        End If
        CZWFStepRelevantToSonWFStep = True
    End Function

    ''' <summary>
    ''' 获取工艺其他版本的子工艺
    ''' </summary>
    ''' <param name="workflowID">工艺ID</param>
    ''' <returns></returns>
    ''' <remarks></remarks>
    Private Function GetSonWFOFOtherWF(ByVal workflowID As String) As DataTable Implements uLMSStepRelevantToSonWFStepIBusiness.GetSonWFOFOtherWF
        Dim strsql As String =
            <string>
                SELECT sonwb.WORKFLOWNAME || ':' || sonwf.WORKFLOWREVISION || '(' || sonwf.WORKFLOWTYPE || ')' WORKFLOWNAME,sonwf.workflowid
				FROM Workflow wf_
				LEFT JOIN Workflowbase wb ON wb.workflowbaseid = wf_.workflowbaseid
				LEFT JOIN Workflow wf ON wf.workflowbaseid = wb.workflowbaseid
				LEFT JOIN stepsubstep sbs ON sbs.workflowid = wf.workflowid
				LEFT JOIN Workflow sonwf ON sonwf.workflowid = sbs.sonworkflowid
				LEFT JOIN Workflowbase sonwb ON sonwb.workflowbaseid = sonwf.workflowbaseid
            </string>
        strsql += String.Format(" WHERE wf_.workflowid = '{0}' and sonwf.workflowid is not null", workflowID)
        strsql += " GROUP BY sonwb.WORKFLOWNAME,sonwf.WORKFLOWREVISION,sonwf.WORKFLOWTYPE,sonwf.workflowid"
        Return DatabaseHelper.Query_ReturnDatTable(strsql)
    End Function
End Class
